/**
 * 
 */
package org.istt.dao.hibernate;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.istt.dao.CategoryDao;
import org.istt.domain.Category;
import org.istt.domain.Field;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

/**
 * @author Huy
 * 
 */
@Repository("categoryDao")
public class CategoryDaoHibernate extends BaseDaoHibernate<Category, Long>
		implements CategoryDao {
	@Autowired
	public CategoryDaoHibernate(SessionFactory sessionFactory) {
		super(sessionFactory);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Category> findByEntity(final Category c,
			final String... excludeProperty) {
		return (List<Category>) hibernateTemplate
				.execute(new HibernateCallback<Object>() {

					@Override
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						Criteria crit = session
								.createCriteria(getPersistentClass());
						Example example = Example.create(c);
						for (String exclude : excludeProperty) {
							example.excludeProperty(exclude);
						}
						example.excludeZeroes().enableLike(MatchMode.ANYWHERE);
						crit.add(example);
						
						if (c.getWorkflow() != null) {
							crit.createCriteria("workflow").add(Restrictions.idEq(c.getWorkflow().getId()));
						}
						
						if (c.getField() != null) {
							crit.createCriteria("field").add(Restrictions.idEq(c.getField().getId()));
						}
						return crit.list();
					}
				});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Category> findByFieldId(final Long fieldId) {

		return (List<Category>) hibernateTemplate
				.execute(new HibernateCallback<Object>() {

					@Override
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						Criteria crit = session
								.createCriteria(getPersistentClass());
						Field f = new Field();
						f.setId(fieldId);
						crit.add(Restrictions.eq("field", f));
						crit.addOrder(Order.asc("categoryName"));
						return crit.list();
					}
				});
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> statisticsCategory (final Long fieldId, final Long userId, final Date fromDate, final Date toDate) {

		return (List<Object[]>) hibernateTemplate
				.execute(new HibernateCallback<Object>() {

					@Override
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						StringBuilder builder = new StringBuilder();
						builder.append(" SELECT q2.CATEGORY_ID AS CATEGORY_ID,t1.CATEGORY_NAME AS CATEGORY_NAME, q2.SUM AS SUM,t4.STATUS AS STATUS, q2.POSITION AS POSITION,t4.ID AS PROCESS_STATUS_ID FROM "); 
						builder.append(" ( ");
						builder.append(" SELECT q1.CATEGORY_ID, COUNT(q1.ID) AS SUM,q1.POSITION FROM ( ");
						builder.append(" SELECT u1.ID AS ID, MAX(u3.POSITION) AS POSITION, u5.ID AS CATEGORY_ID ");
						builder.append(" FROM TBL_FILE u1  ");
						builder.append(" INNER JOIN TBL_FILE_MANAGEMENT u2 ON u1.ID=u2.FILE_ID ");
						builder.append(" INNER JOIN TBL_WORKFLOW_PROCESS u3 ON u2.WORKFLOW_PROCESS_ID=u3.ID ");
						builder.append(" INNER JOIN TBL_PROCESS_STATUS u4 ON u3.PROCESS_STATUS_ID=u4.ID ");
						builder.append(" INNER JOIN TBL_CATEGORY u5 ON u1.CATEGORY_ID=u5.ID ");
						builder.append(" INNER JOIN TBL_FIELD u6 ON u5.FIELD_ID=u6.ID WHERE (u2.ACTIVE_STATUS='true')");
						
						if ((fieldId != null)&&((fieldId != 0))) {
							builder.append(" AND u6.ID= :fieldId ");
						}
						if ((userId != null)&&((userId != 0))) {
							builder.append(" AND u2.APPROVER_ID= :userId ");
						}
						if (fromDate != null) {
							builder.append(" AND u2.RECEIVED_DATE >= :fromDate" );
						}
						if (toDate != null) {
							builder.append(" AND u2.APPROVED_DATE <= :toDate" );
						}						
						
						builder.append(" GROUP BY u1.ID,u5.ID ");
						builder.append(" ) AS q1 GROUP BY q1.POSITION,q1.CATEGORY_ID ");
						builder.append(" ) AS q2  ");
						builder.append(" INNER JOIN TBL_CATEGORY t1 ON q2.CATEGORY_ID=t1.ID ");
						builder.append(" INNER JOIN TBL_WORKFLOW t2 ON t1.WORKFLOW_ID =t2.ID ");
						builder.append(" INNER JOIN TBL_WORKFLOW_PROCESS t3 ON t2.ID=t3.WORKFLOW_ID ");
						builder.append(" INNER JOIN TBL_PROCESS_STATUS t4 ON t3.PROCESS_STATUS_ID =t4.ID ");
						builder.append(" WHERE t3.POSITION=q2.POSITION ORDER BY CATEGORY_ID,PROCESS_STATUS_ID ");
						
						SQLQuery sql = session.createSQLQuery(builder.toString())
						.addScalar("CATEGORY_ID", Hibernate.LONG)
						.addScalar("CATEGORY_NAME", Hibernate.STRING)
						.addScalar("SUM", Hibernate.INTEGER)
						//.addScalar("POSITION", Hibernate.INTEGER)
						.addScalar("STATUS", Hibernate.STRING)
						.addScalar("PROCESS_STATUS_ID", Hibernate.LONG)
						;
						if (fromDate != null) {
							sql.setDate("fromDate", fromDate);
						}
						if (toDate != null) {
							sql.setDate("toDate", toDate);
						}						
						if ((fieldId != null)&&((fieldId != 0))) {
							sql.setLong("fieldId", fieldId);
						}
						if ((userId != null)&&((userId != 0))) {
							sql.setLong("userId", userId);
						}						
						return sql.list();
					}
				});
	}	
	

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getProcessStatusId() {

		return (List<Object[]>) hibernateTemplate
				.execute(new HibernateCallback<Object>() {

					@Override
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						StringBuilder builder = new StringBuilder();
						builder.append(" SELECT ID AS ID,STATUS AS STATUS "); 
						builder.append(" FROM TBL_PROCESS_STATUS ");
						
						SQLQuery sql = session.createSQLQuery(builder.toString())
						.addScalar("ID", Hibernate.LONG)
						.addScalar("STATUS", Hibernate.STRING)
						;
						
						return sql.list();
					}
				});
	}		
	
	public boolean checkFileByCategory(){
		return true;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Category> findByDeptId(final Long deptId) {
		return (List<Category>) hibernateTemplate.execute(new HibernateCallback<Object>() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Criteria criteria = session.createCriteria(Category.class)
				.setFetchMode("Department", FetchMode.JOIN)
				.setFetchMode("Field", FetchMode.JOIN);
				criteria.createAlias("field", "t1");
				criteria.createAlias("t1.department", "t2");
				criteria.addOrder(Order.asc("categoryName"));
				if (deptId != null) {
					criteria.add(Restrictions.eq("t2.id", deptId));
				}
				return criteria.list();

			}
		});	
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Category> findByAllowSubmitOnline() {
		return (List<Category>) hibernateTemplate.execute(new HibernateCallback<Object>() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Criteria crit = session.createCriteria(getPersistentClass());
					crit.add(Restrictions.eq("isViewOnnet", true));
					
				return crit.list();
			}
		});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Category> getSortedList() {
		return (List<Category>) hibernateTemplate.execute(new HibernateCallback<Object>() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				Criteria crit = session.createCriteria(getPersistentClass());
				crit.addOrder(Order.asc("categoryName"));
				
				return crit.list();
			}
		});
	}
	
}
